package com.product.home.server;

import com.product.home.server.databaseDAO.DatabaseTable;

public abstract class StudentQueries 
{
	public static final String getStudentsList="SELECT S.STUDENT_ID,P.FIRST_NAME, P.MIDDLE_NAME, P.LAST_NAME, P.GENDER, C.COURSE_NAME,SE.SECTION_NAME,S.ADMISION_NO, S.ADMISION_DATE,S.PARENT_ID FROM PERSON_DATA AS P JOIN STUDENT_DATA AS S JOIN COURSES AS C JOIN SECTIONS AS SE ON P.PERSON_ID=S.PERSON_ID AND S.COURSE_ID=C.COURSE_ID AND S.SECTION_ID=SE.SECTION_ID";
	
	//public static final String getStudentsList="SELECT s.STUDENT_ID,p.FIRST_NAME,p.MIDDLE_NAME,p.LAST_NAME,s.ADMISION_NO,s.ADMISION_DATE,c.COURSE_NAME FROM "+DatabaseTable.PERSON_DATA+" as p join "+DatabaseTable.STUDENT_DATA+" as s join "+DatabaseTable.COURSES+" as c on p.PERSON_ID=s.PERSON_ID and c.COURSE_ID=s.COURSE_ID";
	
	public static final String insertStudentData="INSERT INTO "+DatabaseTable.STUDENT_DATA+"(STUDENT_ID,`PERSON_ID`, `ADMISION_DATE`, `COURSE_ID`, `SECTION_ID`, `PHOTO`, `PARENT_ID`, `STATUS`,FEE,`ADMISION_NO`) select NULL,?,?,?,?,'-',0,1,?,case when count(*)=0 then '1' else MAX( CONVERT(`ADMISION_NO`,UNSIGNED)+1) end from "+DatabaseTable.STUDENT_DATA;
	
	public static final String getStudentDetails="SELECT P.FIRST_NAME, P.MIDDLE_NAME, P.LAST_NAME, P.DOB, P.GENDER, P.RELIGION, P.ADDRESS1, P.ADDRESS2, P.CITY, P.STATE, P.PINCODE, P.COUNTRY, P.PHONE1, P.PHONE2, P.BLOOD_GROUP, P.BIRTH_PLACE, P.NATIONALITY, P.LANGUAGE, P.EMAIL,C.COURSE_NAME,C.COURSE_ID,SE.SECTION_NAME,SE.SECTION_ID,S.ADMISION_NO, S.ADMISION_DATE,S.PHOTO,S.PARENT_ID,S.FEE FROM "+DatabaseTable.PERSON_DATA+" AS P JOIN "+DatabaseTable.STUDENT_DATA+" AS S JOIN "+DatabaseTable.COURSES+" AS C JOIN "+DatabaseTable.SECTIONS+" AS SE ON P.PERSON_ID=S.PERSON_ID AND S.COURSE_ID=C.COURSE_ID AND S.SECTION_ID=SE.SECTION_ID WHERE S.STUDENT_ID=?";
	
	public static final String insertPersonData="INSERT INTO "+DatabaseTable.PERSON_DATA+"(`FIRST_NAME`, `MIDDLE_NAME`, `LAST_NAME`, `DOB`, `GENDER`, `RELIGION`, `ADDRESS1`, `ADDRESS2`, `CITY`, `STATE`, `PINCODE`, `COUNTRY`, `PHONE1`, `PHONE2`, `BLOOD_GROUP`, `BIRTH_PLACE`, `NATIONALITY`, `LANGUAGE`, `EMAIL`,`PERSON_ID`) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NULL)";
	
	public static final String insertParentData="INSERT INTO `PARENT_DATA`(`PERSON_ID`, `EDUCATION`, `OCCUPATION`, `RELATIONTYPE`, `INCOME`, `STUDENT_ID`, `STATUS`) VALUES (?,?,?,?,?,?,'1')";
	
	public static final String updateStudentParentid="UPDATE `STUDENT_DATA` SET `PARENT_ID`=? WHERE `STUDENT_ID`=?";
	
	public static final String insertLoginData="INSERT INTO `LOGIN_DATA`(`USERNAME`, `PASSWORD`, `ROLE_ID`, `STATUS`, `STUDENT_PARENT_ID`) VALUES (?,?,?,?,?)";
	
	public static final String insetLoginData_student="INSERT INTO `LOGIN_DATA`(`USERNAME`, `PASSWORD`, `ROLE_ID`, `STATUS`, `STUDENT_PARENT_ID`) SELECT  `ADMISION_NO`,sha1(ADMISION_NO),'3','1',? FROM `STUDENT_DATA` where STUDENT_ID=?";
	
	public static final String getHighestAdmisionnumber="select case when count(*)=0 then '1' else MAX( CONVERT(`ADMISION_NO`,UNSIGNED))+1 end as ADMISION_NO from "+DatabaseTable.STUDENT_DATA;
	
	public static final String getParentData="SELECT PD.`PARENT_ID`,PD.`EDUCATION`, PD.`OCCUPATION`, PD.`RELATIONTYPE`, PD.`INCOME`,PERDATA.`FIRST_NAME`,PERDATA.`LAST_NAME`, PERDATA.`DOB`, PERDATA.`GENDER`, PERDATA.`ADDRESS1`, PERDATA.`ADDRESS2`, PERDATA.`CITY`, PERDATA.`STATE`, PERDATA.`PINCODE`, PERDATA.`COUNTRY`, PERDATA.`PHONE1`, PERDATA.`PHONE2`, PERDATA.`EMAIL` FROM "+DatabaseTable.PARENT_DATA+" AS PD JOIN "+DatabaseTable.PERSON_DATA+" AS PERDATA ON PD.PERSON_ID=PERDATA.PERSON_ID where PARENT_ID=?";
}
